﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Configuration;

namespace MyBank
{
    // NOTE: If you change the class name "ServiceMyBank" here, you must also update the reference to "ServiceMyBank" in Web.config.
    public class ServiceMyBank : IServiceMyBank
    {
        public string TransferToGroupB1(int fromAccount, String fromPass)
        {
            float money = 150000;
            string result = "";
            string strConnectionString = ConfigurationManager.ConnectionStrings["OleDbConnectionString"].ToString();
            OleDbConnection cnn = new OleDbConnection(strConnectionString);
            OleDbTransaction trans = null;
            string strQuery = "";

            try
            {
                cnn.Open();
                trans = cnn.BeginTransaction(IsolationLevel.ReadCommitted);

                // Get Customer Account Balance
                strQuery = string.Format("Select Balance from Accounts where AccountID={0} and Password='{1}'", fromAccount, fromPass);
                OleDbCommand cmd = new OleDbCommand(strQuery, cnn, trans);
                float balance = float.Parse(cmd.ExecuteScalar().ToString());

                if (balance < money)
                {
                    throw new Exception("Your account does not have enough money!");
                }

                // Get Telecom account ID
                strQuery = "Select AccountID from Accounts where AccountName='Group_B1'";
                cmd.CommandText = strQuery;
                int b1Account = (int)cmd.ExecuteScalar();

                // Transfer Money to Telecom Account
                strQuery = string.Format("insert into Transfers(FromAccount, ToAccount, Total, [Date]) values ({0}, {1}, {2}, '{3}')",
                                        fromAccount, b1Account, money, DateTime.Now.ToString());
                cmd.CommandText = strQuery;

                if (cmd.ExecuteNonQuery() > 0)
                {
                    // Update Balance Accounts
                    strQuery = string.Format("update Accounts set Balance=Balance-{0} where AccountID={1}",
                                money, fromAccount);
                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();

                    strQuery = string.Format("update Accounts set Balance=Balance+{0} where AccountID={1}",
                                money, b1Account);
                    cmd.CommandText = strQuery;
                    cmd.ExecuteNonQuery();
                }

                // Commit the transaction. 
                trans.Commit();
                result = "Success";
            }
            catch (Exception exception)
            {
                if (trans != null)
                    trans.Rollback();
                result = exception.Message;
            }
            finally
            {
                // Close the connection. 
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
            }

            return result;
        }
    }
}
